
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_FactSales_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_FactSales_Insert]
GO


create procedure dbo.p_FactSales_Insert (
	@SalesDateKey int,
	@CommodityKey int,
	@CommodityGroupKey int,
	@DivisionKey int,
	@CustomerKey int,
	@CustomerDeliveryAddressKey int,
	@CustomerBillingAddressKey int,
	@MerchantKey int,
	@SubMerchantKey int,
	@DeliveryTypeKey int,
	@Quantity int,
	@LocalValue numeric (18, 3),
	@LocalValueVAT numeric (18, 3),
	@DeliveryValue numeric (18, 3),
	@DeliveryValueVAT numeric (18, 3),
	@LocalTotalValue numeric (18, 3),
	@DivisionTotalValue numeric (18, 3),
	@CorporateTotalValue numeric (18, 3))
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnFactSalesInsert
        else
	    save transaction txnFactSalesInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	if (not exists (select 1 
		          from dbo.DimDate with (NOLOCK)
			  where (DateKey = @SalesDateKey)))
	begin

		set @Error = -3
		goto cleanup

	end

	if (not exists (select 1 
		          from dbo.DimCommodity with (NOLOCK)
			  where (CommodityKey = @CommodityKey)))
	begin

		set @Error = -4
		goto cleanup

	end	

	if (not exists (select 1 
		          from dbo.DimDivisionKey with (NOLOCK)
			  where (DivisionKey = @DivisionKey)))
	begin

		set @Error = -5
		goto cleanup

	end

	if (not exists (select 1 
		          from dbo.DimCustomer with (NOLOCK)
			  where (CustomerKey = @CustomerKey)))
	begin

		set @Error = -6
		goto cleanup

	end	

	if (not exists (select 1 
		          from dbo.DimAddress with (NOLOCK)
			  where (AddressKey = @CustomerDeliveryAddressKey)))
	begin

		set @Error = -7
		goto cleanup

	end	

	if (not exists (select 1 
		          from dbo.DimAddress with (NOLOCK)
			  where (AddressKey = @CustomerBillingAddressKey)))
	begin

		set @Error = -8
		goto cleanup

	end

	if (not exists (select 1 
		          from dbo.DimMerchant with (NOLOCK)
			  where (MerchantKey = @MerchantKey)))
	begin

		set @Error = -9
		goto cleanup

	end

	if (not exists (select 1 
		          from dbo.DimSubMerchant with (NOLOCK)
			  where (SubMerchantKey = @SubMerchantKey)))
	begin

		set @Error = -10
		goto cleanup

	end

	if (not exists (select 1 
		          from dbo.DimDeliveryType with (NOLOCK)
			  where (DeliveryTypeKey = @DeliveryTypeKey)))
	begin

		set @Error = -11
		goto cleanup

	end

	
	update dbo.FactSales set Quantity = Quantity + @Quantity,
				 LocalValue = LocalValue + @LocalValue,
				 LocalValueVAT = LocalValueVAT + @LocalValueVAT,
				 DeliveryValue = DeliveryValue + @DeliveryValue,
				 DeliveryValueVAT = DeliveryValueVAT + @DeliveryValueVAT,
				 LocalTotalValue = LocalTotalValue + @LocalTotalValue,
				 DivisionTotalValue = DivisionTotalValue + @DivisionTotalValue,
				 CorporateTotalValue = CorporateTotalValue + @CorporateTotalValue,
				 IsUpdated = 1
	  where ((SalesDateKey = @SalesDateKey) and
	         (CommodityKey = @CommodityKey) and
	         (CommodityGroupKey = @CommodityGroupKey) and
	         (DivisionKey = @DivisionKey) and
	         (CustomerKey = @CustomerKey) and
	         (CustomerDeliveryAddressKey = @CustomerDeliveryAddressKey) and
	         (CustomerBillingAddressKey = @CustomerBillingAddressKey) and
	         (MerchantKey = @MerchantKey) and
	         (SubMerchantKey = @SubMerchantKey) and
	         (DeliverTypeKey = @DeliveryTypeKey))
	select @Error = @@error,
	       @Rows = @@rowcount
	if (@Error <> 0) goto cleanup
	
	if (@Rows = 0)
	begin

		insert into dbo.FactSales (SalesDateKey,
					   CommodityKey,
					   CommodityGroupKey,
					   DivisionKey,
					   CustomerKey,
					   CustomerDeliveryAddressKey,
					   CustomerBillingAddressKey,
					   MerchantKey,
					   SubMerchantKey,
					   DeliveryTypeKey,
					   Quantity,
					   LocalValue,
					   LocalValueVAT,
					   DeliveryValue,
					   DeliveryValueVAT,
					   LocalTotalValue,
					   DivisionTotalValue,
					   CorporateTotalValue,
					   IsUpdated)
		  values (@SalesDateKey,
			  @CommodityKey,
			  @CommodityGroupKey,
			  @DivisionKey,
			  @CustomerKey,
			  @CustomerDeliveryAddressKey,
			  @CustomerBillingAddressKey,
			  @MerchantKey,
			  @SubMerchantKey,
			  @DeliveryTypeKey,
			  @Quantity,
			  @LocalValue,
			  @LocalValueVAT,
			  @DeliveryValue,
			  @DeliveryValueVAT,
			  @LocalTotalValue,
			  @DivisionTotalValue,
			  @CorporateTotalValue,
			  1)


		select @Error = @@error,
		       @Rows = @@rowcount
		if ((@Error = 0) and
		    (@Rows = 0)) set @Error = -1
		if (@Error <> 0) goto cleanup

	end
	


cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnFactSalesInsert
		else
		if (@inTranCount = 0) commit transaction txnFactSalesInsert

	end

	return @Error

end
GO

grant execute on dbo.p_FactSales_Insert  to [db_datawriter]
go
